﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

using Oracle.ManagedDataAccess.Client;

namespace WFL.Data.Oracle
{
    public class OracleOperation
    {

        #region 静态方法

        #region ExecuteNonQuery

        public static int ExecuteNonQuery(string sql, OracleConnection connection)
        {
            return ExecuteNonQuery(sql, CommandType.Text, connection, null, null);
        }

        public static int ExecuteNonQuery(string sql, CommandType commandType, OracleConnection connection)
        {
            return ExecuteNonQuery(sql, commandType, connection, null, null);
        }

        public static int ExecuteNonQuery(string sql, OracleConnection connection, OracleTransaction transaction)
        {
            return ExecuteNonQuery(sql, CommandType.Text, connection, transaction, null);
        }

        public static int ExecuteNonQuery(string sql, CommandType commandType, OracleConnection connection, OracleTransaction transaction)
        {
            return ExecuteNonQuery(sql, commandType, connection, transaction, null);
        }

        public static int ExecuteNonQuery(string sql, OracleConnection connection, OracleParameter[] parameters)
        {
            return ExecuteNonQuery(sql, CommandType.Text, connection, null, parameters);
        }

        public static int ExecuteNonQuery(string sql, CommandType commandType, OracleConnection connection, OracleParameter[] parameters)
        {
            return ExecuteNonQuery(sql, commandType, connection, null, parameters);
        }

        public static int ExecuteNonQuery(string sql, OracleConnection connection, OracleTransaction transaction, OracleParameter[] parameters)
        {
            return ExecuteNonQuery(sql, CommandType.Text, connection, transaction, parameters);
        }

        public static int ExecuteNonQuery(string sql, CommandType commandType, OracleConnection connection, OracleTransaction transaction, OracleParameter[] parameters)
        {
            OracleCommand comm = new OracleCommand();
            comm.Connection = connection;
#if ora10g
            sql = sql.Replace("\r\n", "  ");
#endif
            comm.CommandText = sql;
            comm.CommandType = commandType;
            comm.Transaction = transaction;
            comm.BindByName = true;
            if (parameters != null)
            {
                comm.Parameters.AddRange(parameters);
            }
            return comm.ExecuteNonQuery();
        }

        #endregion


        #region ExecuteReader

        public static OracleDataReader ExecuteReader(string sql, OracleConnection connection)
        {
            return ExecuteReader(sql, CommandType.Text, connection, null, null);
        }

        public static OracleDataReader ExecuteReader(string sql, CommandType commandType, OracleConnection connection)
        {
            return ExecuteReader(sql, commandType, connection, null, null);
        }

        public static OracleDataReader ExecuteReader(string sql, OracleConnection connection, OracleTransaction transaction)
        {
            return ExecuteReader(sql, CommandType.Text, connection, transaction, null);
        }

        public static OracleDataReader ExecuteReader(string sql, CommandType commandType, OracleConnection connection, OracleTransaction transaction)
        {
            return ExecuteReader(sql, commandType, connection, transaction, null);
        }

        public static OracleDataReader ExecuteReader(string sql, OracleConnection connection, OracleParameter[] parameters)
        {
            return ExecuteReader(sql, CommandType.Text, connection, null, parameters);
        }

        public static OracleDataReader ExecuteReader(string sql, CommandType commandType, OracleConnection connection, OracleParameter[] parameters)
        {
            return ExecuteReader(sql, commandType, connection, null, parameters);
        }

        public static OracleDataReader ExecuteReader(string sql, OracleConnection connection, OracleTransaction transaction, OracleParameter[] parameters)
        {
            return ExecuteReader(sql, CommandType.Text, connection, transaction, parameters);
        }

        public static OracleDataReader ExecuteReader(string sql, CommandType commandType, OracleConnection connection, OracleTransaction transaction, OracleParameter[] parameters)
        {
            OracleCommand comm = new OracleCommand();
            comm.Connection = connection;
#if ora10g
            sql = sql.Replace("\r\n", "  ");
#endif
            comm.CommandText = sql;
            comm.CommandType = commandType;
            comm.Transaction = transaction;
            comm.BindByName = true;
            if (parameters != null)
            {
                comm.Parameters.AddRange(parameters);
            }
            return comm.ExecuteReader();
        }

        #endregion


        #region ExecuteScalar

        public static object ExecuteScalar(string sql, OracleConnection connection)
        {
            return ExecuteScalar(sql, CommandType.Text, connection, null, null);
        }

        public static object ExecuteScalar(string sql, CommandType commandType, OracleConnection connection)
        {
            return ExecuteScalar(sql, commandType, connection, null, null);
        }

        public static object ExecuteScalar(string sql, OracleConnection connection, OracleTransaction transaction)
        {
            return ExecuteScalar(sql, CommandType.Text, connection, transaction, null);
        }

        public static object ExecuteScalar(string sql, CommandType commandType, OracleConnection connection, OracleTransaction transaction)
        {
            return ExecuteScalar(sql, commandType, connection, transaction, null);
        }

        public static object ExecuteScalar(string sql, OracleConnection connection, OracleParameter[] parameters)
        {
            return ExecuteScalar(sql, CommandType.Text, connection, null, parameters);
        }

        public static object ExecuteScalar(string sql, CommandType commandType, OracleConnection connection, OracleParameter[] parameters)
        {
            return ExecuteScalar(sql, commandType, connection, null, parameters);
        }

        public static object ExecuteScalar(string sql, OracleConnection connection, OracleTransaction transaction, OracleParameter[] parameters)
        {
            return ExecuteScalar(sql, CommandType.Text, connection, transaction, parameters);
        }

        public static object ExecuteScalar(string sql, CommandType commandType, OracleConnection connection, OracleTransaction transaction, OracleParameter[] parameters)
        {
            OracleCommand comm = new OracleCommand();
            comm.Connection = connection;
#if ora10g
            sql = sql.Replace("\r\n", "  ");
#endif
            comm.CommandText = sql;
            comm.CommandType = commandType;
            comm.Transaction = transaction;
            comm.BindByName = true;
            if (parameters != null)
            {
                comm.Parameters.AddRange(parameters);
            }
            return comm.ExecuteScalar();
        }

        #endregion


        #region ExecuteDataSet

        public static DataSet ExecuteDataSet(string sql, OracleConnection connection)
        {
            return ExecuteDataSet(sql, CommandType.Text, connection, null, null);
        }

        public static DataSet ExecuteDataSet(string sql, CommandType commandType, OracleConnection connection)
        {
            return ExecuteDataSet(sql, commandType, connection, null, null);
        }

        public static DataSet ExecuteDataSet(string sql, OracleConnection connection, OracleTransaction transaction)
        {
            return ExecuteDataSet(sql, CommandType.Text, connection, transaction, null);
        }

        public static DataSet ExecuteDataSet(string sql, CommandType commandType, OracleConnection connection, OracleTransaction transaction)
        {
            return ExecuteDataSet(sql, commandType, connection, transaction, null);
        }

        public static DataSet ExecuteDataSet(string sql, OracleConnection connection, OracleParameter[] parameters)
        {
            return ExecuteDataSet(sql, CommandType.Text, connection, null, parameters);
        }

        public static DataSet ExecuteDataSet(string sql, CommandType commandType, OracleConnection connection, OracleParameter[] parameters)
        {
            return ExecuteDataSet(sql, commandType, connection, null, parameters);
        }

        public static DataSet ExecuteDataSet(string sql, OracleConnection connection, OracleTransaction transaction, OracleParameter[] parameters)
        {
            return ExecuteDataSet(sql, CommandType.Text, connection, transaction, parameters);
        }

        public static DataSet ExecuteDataSet(string sql, CommandType commandType, OracleConnection connection, OracleTransaction transaction, OracleParameter[] parameters)
        {
            OracleCommand comm = new OracleCommand();
            comm.Connection = connection;
#if ora10g
            sql = sql.Replace("\r\n", "  ");
#endif
            comm.CommandText = sql;
            comm.CommandType = commandType;
            comm.Transaction = transaction;
            comm.BindByName = true;
            if (parameters != null)
            {
                comm.Parameters.AddRange(parameters);
            }
            OracleDataAdapter adapter = new OracleDataAdapter(comm);
            DataSet result = new DataSet();
            adapter.Fill(result);
            return result;
        }

        #endregion

        #endregion


        public string ConnectionString
        {
            get;
            set;
        }

        public OracleOperation()
        { 
        
        }

        public OracleOperation(string connectionString)
        {
            ConnectionString = connectionString;
        }

        protected virtual OracleConnection GetContextConnection()
        {
            return null;
        }

        protected virtual OracleTransaction GetContextTransaction()
        {
            return null;
        }

        private OracleConnection GetConnection(out bool creat)
        {
            creat = false;
            OracleConnection connection = GetContextConnection();
            if (connection == null)
            {
                connection = new OracleConnection(ConnectionString);
                connection.Open();
                creat = true;
            }
            return connection;
        }

        private OracleTransaction BeginTransaction(OracleConnection connection, IsolationLevel iso, out bool newTrans)
        {
            newTrans = false;
            OracleTransaction transaction = GetContextTransaction();
            if (transaction != null)
            {
                if (transaction.IsolationLevel != iso)
                {
                    throw new Exception("上下文中存在不同隔离级别的事务。");
                }
            }
            else
            {
                transaction = connection.BeginTransaction(iso);
                newTrans = true;
            }
            return transaction;
        }


        #region ExecuteNonQuery

        public int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, CommandType.Text, (OracleParameter[])null);
        }

        public int ExecuteNonQuery(string sql, CommandType commandType)
        {
            return ExecuteNonQuery(sql, commandType, (OracleParameter[])null);
        }

        public int ExecuteNonQuery(string sql, OracleParameter[] parameters)
        {
            return ExecuteNonQuery(sql, CommandType.Text, parameters);
        }

        public int ExecuteNonQuery(string sql, CommandType commandType, OracleParameter[] parameters)
        {
            bool creat = false;
            OracleConnection connection = GetConnection(out creat);
            try
            {
                return ExecuteNonQuery(sql, commandType, connection, GetContextTransaction(), parameters);
            }
            finally
            {
                if (creat)
                {
                    connection.Close();
                }
            }
        }

        public int ExecuteNonQuery(string sql, IsolationLevel iso)
        {
            return ExecuteNonQuery(sql, CommandType.Text, null, iso);
        }

        public int ExecuteNonQuery(string sql, CommandType commandType, IsolationLevel iso)
        {
            return ExecuteNonQuery(sql, commandType, null, iso);
        }

        public int ExecuteNonQuery(string sql, OracleParameter[] parameters, IsolationLevel iso)
        {
            return ExecuteNonQuery(sql, CommandType.Text, parameters, iso);
        }

        public int ExecuteNonQuery(string sql, CommandType commandType, OracleParameter[] parameters, IsolationLevel iso)
        {
            bool creat = false;
            OracleConnection connection = GetConnection(out creat);
            OracleTransaction transaction = null;
            bool newTrans = false;
            try
            {
                transaction = BeginTransaction(connection, iso, out newTrans);
                int result = ExecuteNonQuery(sql, commandType, connection, transaction, parameters);
                if (newTrans)
                {
                    transaction.Commit();
                }
                return result;
            }
            catch
            {
                if (transaction != null
                    && newTrans)
                {
                    transaction.Rollback();
                }
                throw;
            }
            finally
            {
                if (creat)
                {
                    connection.Close();
                }
            }
        }

        #endregion


        #region ExecuteScalar

        public object ExecuteScalar(string sql)
        {
            return ExecuteScalar(sql, CommandType.Text, (OracleParameter[])null);
        }

        public object ExecuteScalar(string sql, CommandType commandType)
        {
            return ExecuteScalar(sql, commandType, (OracleParameter[])null);
        }

        public object ExecuteScalar(string sql, OracleParameter[] parameters)
        {
            return ExecuteScalar(sql, CommandType.Text, parameters);
        }

        public object ExecuteScalar(string sql, CommandType commandType, OracleParameter[] parameters)
        {
            bool creat = false;
            OracleConnection connection = GetConnection(out creat);
            try
            {
                return ExecuteScalar(sql, commandType, connection, GetContextTransaction(), parameters);
            }
            finally
            {
                if (creat)
                {
                    connection.Close();
                }
            }
        }

        public object ExecuteScalar(string sql, IsolationLevel iso)
        {
            return ExecuteScalar(sql, CommandType.Text, null, iso);
        }

        public object ExecuteScalar(string sql, CommandType commandType, IsolationLevel iso)
        {
            return ExecuteScalar(sql, commandType, null, iso);
        }

        public object ExecuteScalar(string sql, OracleParameter[] parameters, IsolationLevel iso)
        {
            return ExecuteScalar(sql, CommandType.Text, parameters, iso);
        }

        public object ExecuteScalar(string sql, CommandType commandType, OracleParameter[] parameters, IsolationLevel iso)
        {
            bool creat = false;
            OracleConnection connection = GetConnection(out creat);
            OracleTransaction transaction = null;
            bool newTrans = false;
            try
            {
                transaction = BeginTransaction(connection, iso, out newTrans);
                object result = ExecuteScalar(sql, commandType, connection, transaction, parameters);
                if (newTrans)
                {
                    transaction.Commit();
                }
                return result;
            }
            catch
            {
                if (transaction != null
                    && newTrans)
                {
                    transaction.Rollback();
                }
                throw;
            }
            finally
            {
                if (creat)
                {
                    connection.Close();
                }
            }
        }

        #endregion


        #region ExecuteDataSet

        public DataSet ExecuteDataSet(string sql)
        {
            return ExecuteDataSet(sql, CommandType.Text, (OracleParameter[])null);
        }

        public DataSet ExecuteDataSet(string sql, CommandType commandType)
        {
            return ExecuteDataSet(sql, commandType, (OracleParameter[])null);
        }

        public DataSet ExecuteDataSet(string sql, OracleParameter[] parameters)
        {
            return ExecuteDataSet(sql, CommandType.Text, parameters);
        }

        public DataSet ExecuteDataSet(string sql, CommandType commandType, OracleParameter[] parameters)
        {
            bool creat = false;
            OracleConnection connection = GetConnection(out creat);
            try
            {
                return ExecuteDataSet(sql, commandType, connection, GetContextTransaction(), parameters);
            }
            finally
            {
                if (creat)
                {
                    connection.Close();
                }
            }
        }

        public DataSet ExecuteDataSet(string sql, IsolationLevel iso)
        {
            return ExecuteDataSet(sql, CommandType.Text, null, iso);
        }

        public DataSet ExecuteDataSet(string sql, CommandType commandType, IsolationLevel iso)
        {
            return ExecuteDataSet(sql, commandType, null, iso);
        }

        public DataSet ExecuteDataSet(string sql, OracleParameter[] parameters, IsolationLevel iso)
        {
            return ExecuteDataSet(sql, CommandType.Text, parameters, iso);
        }

        public DataSet ExecuteDataSet(string sql, CommandType commandType, OracleParameter[] parameters, IsolationLevel iso)
        {
            bool creat = false;
            OracleConnection connection = GetConnection(out creat);
            OracleTransaction transaction = null;
            bool newTrans = false;
            try
            {
                transaction = BeginTransaction(connection, iso, out newTrans);
                DataSet result = ExecuteDataSet(sql, commandType, connection, transaction, parameters);
                if (newTrans)
                {
                    transaction.Commit();
                }
                return result;
            }
            catch
            {
                if (transaction != null
                    && newTrans)
                {
                    transaction.Rollback();
                }
                throw;
            }
            finally
            {
                if (creat)
                {
                    connection.Close();
                }
            }
        }

        #endregion


        #region ExecuteReaderExtension

        public OracleDataReaderExtension ExecuteReaderExtension(string sql)
        {
            return ExecuteReaderExtension(sql, CommandType.Text, null);
        }

        public OracleDataReaderExtension ExecuteReaderExtension(string sql, OracleParameter[] parameters)
        {
            return ExecuteReaderExtension(sql, CommandType.Text, parameters);
        }

        public OracleDataReaderExtension ExecuteReaderExtension(string sql, CommandType commandType, OracleParameter[] parameters)
        {
            bool creat = false;
            OracleConnection connection = GetConnection(out creat);
            try
            {
                OracleDataReader reader = OracleOperation.ExecuteReader(sql, commandType, connection, GetContextTransaction(), parameters);
                OracleDataReaderExtension result = new OracleDataReaderExtension(reader);
                if (creat)
                {
                    result.Connection = connection;
                }
                return result;
            }
            catch
            {
                if (creat)
                {
                    connection.Close();
                }
                throw;
            }
        }

        public OracleDataReaderExtension ExecuteReaderExtension(string sql, IsolationLevel iso)
        {
            return ExecuteReaderExtension(sql, CommandType.Text, null, iso);
        }

        public OracleDataReaderExtension ExecuteReaderExtension(string sql, OracleParameter[] parameters, IsolationLevel iso)
        {
            return ExecuteReaderExtension(sql, CommandType.Text, parameters, iso);
        }

        public OracleDataReaderExtension ExecuteReaderExtension(string sql, CommandType commandType, OracleParameter[] parameters, IsolationLevel iso)
        {
            bool creat = false;
            OracleConnection connection = GetConnection(out creat);
            OracleTransaction transaction = null;
            bool newTrans = false;
            try
            {
                transaction = BeginTransaction(connection, iso, out newTrans);
                OracleDataReader reader = OracleOperation.ExecuteReader(sql, commandType, connection, transaction, parameters);
                OracleDataReaderExtension result = new OracleDataReaderExtension(reader);
                if (newTrans)
                {
                    result.Transaction = transaction;
                }
                if (creat)
                {
                    result.Connection = connection;
                }
                return result;
            }
            catch
            {
                if (transaction != null
                    && newTrans)
                {
                    transaction.Rollback();
                }
                if (creat)
                {
                    connection.Close();
                }
                throw;
            }
        }

        #endregion


    }
}
